﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TemplateScope;
using Microsoft.Synchronization.ClientServices;
using Windows.Storage;
using System.Runtime.InteropServices.WindowsRuntime;
using Windows.UI.Xaml.Media.Imaging;
using Windows.Storage.Streams;
using TemplateScope;
using SQLitePCL;

namespace Fabrikam.Client.DataModel
{

    public class DataService
    {
        public static string SyncUri = "http://localhost:33205/TemplateScopeSyncService.svc";
        private TemplateScopeOfflineContext SyncContext { get; set; }

        public StorageFolder DatabaseStorageFolder { get; set; }
        public string DatabaseName { get; set; }
        private string localFilePath;

        private static DataService current;
        public static DataService Current
        {
            get { return current ?? (current = new DataService()); }
        }

        public DataService()
        {
            // SQLite Path
            this.DatabaseName = "fabrikamfiber_sqlite.db";
            this.DatabaseStorageFolder = ApplicationData.Current.LocalFolder;
            this.localFilePath = Path.Combine(this.DatabaseStorageFolder.Path, this.DatabaseName);

            // Context
            this.SyncContext = new TemplateScope.TemplateScopeOfflineContext(this.DatabaseName, new Uri(SyncUri, UriKind.Absolute));
            // Definition of the cache controller serialization format:
            this.SyncContext.CacheController.ControllerBehavior.SerializationFormat = SerializationFormat.ODataJSON;

            // Need to specify the filter paramters.
            this.SyncContext.AddScopeParameters("custId", "6");

        }

        public async Task<CacheRefreshStatistics> Sync()
        {
            try
            {
                var result = await this.SyncContext.SynchronizeAsync();

                var dt = result.EndTime.Subtract(result.StartTime);
                Debug.WriteLine("Time : " + (int)dt.TotalMinutes + " minutes. " + dt.Seconds + " s.");

                // For debug time
                if (result.Error != null)
                    Debug.WriteLine(result.Error.Message);

                return result;

            }
            catch (Exception e)
            {
                Debug.WriteLine(e.Message);
                throw;
            }
        }


        internal bool TableExist(string tableName)
        {
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(localFilePath))
                {
                    const string commandText = "SELECT name FROM sqlite_master WHERE type='table' and name= ?";

                    using (var stmt = connection.Prepare(commandText))
                    {
                        stmt.Bind(1, tableName);

                        if (stmt.Step() == SQLiteResult.ROW)
                        {
                            var tableScope = stmt[0] as String;
                            return tableScope == tableName;
                        }

                    }
                }

            }
            catch (SQLiteException ex)
            {
                Debug.WriteLine(ex.Message);
            }
            return false;

        }

        /// <summary>
        /// Check if Database Exist
        /// </summary>
        public async Task<bool> DatabaseExist()
        {
            // Get the isolated storage file for the application.
            StorageFolder applicationFolder = ApplicationData.Current.LocalFolder;

            return await applicationFolder.FileExistsAsync(this.DatabaseName);
        }


        public async Task<List<Images>> GetImages()
        {

            List<Images> imgs = new List<Images>();
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(localFilePath))
                {
                    const string commandText = "SELECT * FROM Images";

                    using (var stmt = connection.Prepare(commandText))
                    {
                        while (stmt.Step() == SQLiteResult.ROW)
                        {
                            Images img = FillImageFromReader(stmt);
                            imgs.Add(img);
                        }
                    }
                }
            }
            catch (SQLiteException ex)
            {
                Debug.WriteLine(ex.Message);
                throw;
            }


            return imgs;
        }


        public async Task<List<ServiceTickets>> GetTickets()
        {

            List<ServiceTickets> serviceTickets = new List<ServiceTickets>();
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(localFilePath))
                {

                    const string commandText = "SELECT * FROM ServiceTickets LIMIT 1000";

                    using (var stmt = connection.Prepare(commandText))
                    {
                        while (stmt.Step() == SQLiteResult.ROW)
                        {
                            ServiceTickets ticket = FillTicketFromReader(stmt);
                            serviceTickets.Add(ticket);
                        }
                    }
                }
            }
            catch (SQLiteException ex)
            {
                Debug.WriteLine(ex.Message);
                throw;
            }


            return serviceTickets;
        }

        public async Task<ServiceTickets> GetTicketById(Guid id)
        {

            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(localFilePath))
                {

                    const string commandText = "SELECT * FROM ServiceTickets Where ServiceTicketID = ?";

                    using (var stmt = connection.Prepare(commandText))
                    {
                        stmt.Bind(1, id.ToString());

                        if (stmt.Step() == SQLiteResult.ROW)
                            return FillTicketFromReader(stmt);
                    }
                }

                return null;
            }
            catch (SQLiteException ex)
            {
                Debug.WriteLine(ex.Message);
                throw;
            }


        }

        public async Task<bool> DeleteTicket(Guid id)
        {

            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(localFilePath))
                {

                    const string commandText = "DELETE FROM ServiceTickets Where ServiceTicketID = ?";

                    using (var stmt = connection.Prepare(commandText))
                    {
                        stmt.Bind(1, id.ToString());

                        if (stmt.Step() == SQLiteResult.ROW)
                            return true;
                    }
                }

                return false;
            }
            catch (SQLiteException ex)
            {
                Debug.WriteLine(ex.Message);
                throw;
            }

        }

        public async Task<List<ServiceTickets>> GetUnclosedTickets()
        {
            List<ServiceTickets> serviceTickets = new List<ServiceTickets>();
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(localFilePath))
                {

                    const string commandText = "SELECT * FROM ServiceTickets where Closed is null";

                    using (var stmt = connection.Prepare(commandText))
                    {
                        while (stmt.Step() == SQLiteResult.ROW)
                        {
                            ServiceTickets ticket = FillTicketFromReader(stmt);
                            serviceTickets.Add(ticket);
                        }
                    }
                }
            }
            catch (SQLiteException ex)
            {
                Debug.WriteLine(ex.Message);
                throw;
            }



            return serviceTickets;
        }

        public async Task<List<Customers>> GetCustomers()
        {
            List<Customers> customers = new List<Customers>();
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(localFilePath))
                {

                    const string commandText = "SELECT * FROM Customers";

                    using (var stmt = connection.Prepare(commandText))
                    {
                        while (stmt.Step() == SQLiteResult.ROW)
                        {
                            Customers customer = FillCustomerFromReader(stmt);
                            customers.Add(customer);
                        }
                    }
                }
            }
            catch (SQLiteException ex)
            {
                Debug.WriteLine(ex.Message);
                throw;
            }


            return customers;
        }

        public async Task<Customers> GetCustomersById(int id)
        {
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(localFilePath))
                {
                    const string commandText = "SELECT * FROM Customers where ID = ?";

                    using (var stmt = connection.Prepare(commandText))
                    {
                        stmt.Bind(1, id);

                        if (stmt.Step() == SQLiteResult.ROW)
                            return FillCustomerFromReader(stmt);
                    }
                }

            }
            catch (SQLiteException ex)
            {
                Debug.WriteLine(ex.Message);
                throw;
            }

            return null;
        }

        public async Task<Images> GetImagesById(Guid imageId)
        {
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(localFilePath))
                {
                    const string commandText = "SELECT * FROM Images where ID = ?";

                    using (var stmt = connection.Prepare(commandText))
                    {
                        stmt.Bind(1, imageId.ToString());

                        if (stmt.Step() == SQLiteResult.ROW)
                            return FillImageFromReader(stmt);
                    }
                }

            }
            catch (SQLiteException ex)
            {
                Debug.WriteLine(ex.Message);
                throw;
            }


            return null;
        }

        internal async Task<BitmapImage> GetBitmapImageFromImageEntity(Images img)
        {
            var bitmapImage = new BitmapImage();
#if NETFX_CORE
            var stream = new InMemoryRandomAccessStream();
            await stream.WriteAsync(img.Image.AsBuffer());
            stream.Seek(0);
#elif WINDOWS_PHONE

            var stream = new MemoryStream();
            await stream.WriteAsync(img.Image, 0, img.Image.Length);
            stream.Seek(0, SeekOrigin.Begin);
#endif

            bitmapImage.SetSource(stream);
            return bitmapImage;

        }


        internal async Task InsertServiceTickets(List<ServiceTickets> serviceTickets)
        {
            using (SQLiteConnection connection = new SQLiteConnection(localFilePath))
            {
                try
                {

                    using (var statement = connection.Prepare("Begin Transaction"))
                    {
                        statement.Step();
                    }

                    const string commandText = "Insert Into ServiceTickets " +
                                                "(Title, Description, StatusValue, EscalationLevel, Opened, Closed, CustomerID, ServiceTicketID) " +
                                                "Values (?, ?, ?, ?, ?, ?, ?, ?)";

                    using (var stmt = connection.Prepare(commandText))
                    {
                        foreach (var serviceTicket in serviceTickets)
                        {
                            BindServiceTicketParameter(stmt, serviceTicket);

                            stmt.Step();

                            stmt.Reset();
                            stmt.ClearBindings();
                        }
                    }
                    using (var statement = connection.Prepare("Commit Transaction"))
                    {
                        statement.Step();
                    }

                }


                catch (SQLiteException ex)
                {
                    Debug.WriteLine(ex.Message);

                    using (var statement = connection.Prepare("Rollback Transaction"))
                    {
                        statement.Step();
                    }
                    throw;
                }
            }
        }



        internal async Task UpdateServiceTickets(IEnumerable<ServiceTickets> serviceTickets)
        {

            using (SQLiteConnection connection = new SQLiteConnection(localFilePath))
            {
                try
                {

                    using (var statement = connection.Prepare("Begin Transaction"))
                    {
                        statement.Step();
                    }

                    const string commandText = "Update ServiceTickets Set " +
                                                "Title = ?, Description = ?, StatusValue = ?, EscalationLevel = ?, " +
                                                "Opened = ?, Closed = ?, CustomerID = ? " +
                                                "Where ServiceTicketID = ?";

                    using (var stmt = connection.Prepare(commandText))
                    {
                        foreach (var serviceTicket in serviceTickets)
                        {
                            BindServiceTicketParameter(stmt, serviceTicket);

                            stmt.Step();

                            stmt.Reset();
                            stmt.ClearBindings();
                        }
                    }
                    using (var statement = connection.Prepare("Commit Transaction"))
                    {
                        statement.Step();
                    }

                }


                catch (SQLiteException ex)
                {
                    Debug.WriteLine(ex.Message);

                    using (var statement = connection.Prepare("Commit Transaction"))
                    {
                        statement.Step();
                    }
                    throw;
                }
            }
        }


        private static Images FillImageFromReader(ISQLiteStatement stmt)
        {
            Images img = new Images();
            img.ID = Guid.Parse((string)stmt[0]);
            img.Name = (string)stmt[1];
            img.Image = (byte[])stmt[2];
            img.ImageFormat = (string)stmt[3];
            img.Length = Convert.ToInt32(stmt[4]);
            img.Width = Convert.ToInt32(stmt[5]);
            img.Height = Convert.ToInt32(stmt[6]);


            return img;
        }
        private static ServiceTickets FillTicketFromReader(ISQLiteStatement stmt)
        {
            ServiceTickets ticket = new ServiceTickets();
            ticket.ServiceTicketID = Guid.Parse((string)stmt[0]);
            ticket.Title = (string)stmt[1];
            ticket.Description = stmt[2] as string;
            ticket.StatusValue = Convert.ToInt32(stmt[3]);
            ticket.EscalationLevel = Convert.ToInt32(stmt[4]);
            DateTime o, c;
            if (DateTime.TryParse((string)stmt[5], out o))
                ticket.Opened = o;
            if (DateTime.TryParse((string)stmt[6], out c))
                ticket.Closed = c;
            ticket.CustomerID = stmt[7] == null ? (int?)null : Convert.ToInt32(stmt[7]);

            return ticket;
        }
        private static Customers FillCustomerFromReader(ISQLiteStatement stmt)
        {
            Customers c = new Customers();

            c.ID = Convert.ToInt32(stmt[0]);
            c.ImageId = stmt[1] == null ? (Guid?)null : Guid.Parse((string)stmt[1]);
            c.FirstName = stmt[2] as string;
            c.LastName = stmt[3] as string;
            c.Address_Street = stmt[4] as string;
            c.Address_City = stmt[5] as string;
            c.Address_State = stmt[6] as string;
            c.Address_Zip = stmt[7] as string;

            return c;
        }


        private static void BindServiceTicketParameter(ISQLiteStatement stmt, ServiceTickets serviceTicket)
        {
            stmt.Bind(1, serviceTicket.Title);
            stmt.Bind(2, serviceTicket.Description);
            stmt.Bind(3, serviceTicket.StatusValue);
            stmt.Bind(4, serviceTicket.EscalationLevel);

            if (serviceTicket.Opened.HasValue)
                stmt.Bind(5, serviceTicket.Opened.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            else
                stmt.Bind(5, null);

            if (serviceTicket.Closed.HasValue)
                stmt.Bind(6, serviceTicket.Closed.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            else
                stmt.Bind(6, null);

            if (serviceTicket.CustomerID.HasValue)
                stmt.Bind(7, serviceTicket.CustomerID.Value);
            else
                stmt.Bind(7, null);

            stmt.Bind(8, serviceTicket.ServiceTicketID.ToString());
        }


        /// <summary>
        /// Remove the Database
        /// </summary>
        public async Task DeleteDatabase()
        {
            // Get the isolated storage file for the application.
            StorageFolder applicationFolder = ApplicationData.Current.LocalFolder;

            var exist = await applicationFolder.FileExistsAsync(this.DatabaseName);

            if (exist)
            {
                StorageFile databaseFile = await applicationFolder.GetFileAsync(this.DatabaseName);
                await databaseFile.DeleteAsync(StorageDeleteOption.PermanentDelete);
            }
        }

    }

}

